﻿CREATE PROCEDURE [dbo].[EmailQueue_Get]
	@timeout INT
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	declare @date datetime
	set @date = getdate()

	BEGIN TRAN


		UPDATE [dbo].[email_Queue]
			 SET [RequestDate] = @date
		 WHERE [BeginSentDate] <= @date
				and datediff(mi, ISNULL([RequestDate], CAST('1900/01/01' AS datetime)), @date) > @timeout 
				and [SendDate] is null  
				and ([ErrorCount] is null or [ErrorCount] < 10)

		SELECT [EmailQueueId]
				,[Recipient]
				,[BccRecipient]
				,[Subject]
				,[Body]
				,[IsHtml]
				--,[BeginSentDate]
				--,[CreateDate]
				--,[RequestDate]
				--,[SendDate]
				--,[ErrorDate]
				--,[ErrorCount]
				--,[ErrorText]
		FROM [dbo].[email_Queue]
		WHERE ISNULL([RequestDate], CAST('1900/01/01' AS datetime)) = @date

	COMMIT TRAN  

	SELECT TOP 1 [BeginSentDate] 
	FROM [dbo].[email_Queue]
	WHERE datediff(mi, ISNULL([RequestDate], CAST('1900/01/01' AS datetime)), @date) > @timeout
		and ([SendDate] is null ) 
		and ([ErrorCount] is null or [ErrorCount] < 10)
	ORDER BY [BeginSentDate]


END